package com.jzdjt.tool;

import java.io.*;  
import java.sql.*;  
import java.util.*;

 
 
  
public class DBHelper {  
     
/*private String sql;    //要传入的sql语句      
public void setSql(String sql) {  
    this.sql = sql;  
}  */
    
private List sqlValues;  //sql语句的参数  
public void setSqlValues(List sqlValues) {  
    this.sqlValues = sqlValues;  
}  
private Connection con;  //连接对象  
    public void setCon(Connection con) {  
    this.con = con;  
}  
  
    public DBHelper(){  
        this.con=getConnection();  //给Connection的对象赋初值  
    }  
      
    /** 
     * 获取数据库连接 
     * @return 
     */  
    private  Connection getConnection(){   
   
        String driver_class=null;  
        String driver_url=null;  
        String database_user=null;  
        String database_password=null;  
        try {  
            InputStream fis=this.getClass().getResourceAsStream("/db.properties");  //加载数据库配置文件到内存中  
            Properties p=new Properties();  
            p.load(fis);  
              
            driver_class=p.getProperty("driver_class");      //获取数据库配置文件  
            driver_url=p.getProperty("driver_url");  
            database_user=p.getProperty("database_user");  
            database_password=p.getProperty("database_password");  
      
              
            Class.forName(driver_class);  
            con=DriverManager.getConnection(driver_url,database_user,database_password);  
              
              
        } catch (ClassNotFoundException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        } catch (SQLException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        } catch (FileNotFoundException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        } catch (IOException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
        return con;  
    }  
      
    
      
  
    /** 
     * 关闭数据库 
     * @param con 
     * @param pst 
     * @param rst 
     */  
    private  void closeAll(Connection con,PreparedStatement pst,ResultSet rst){  
        if(rst!=null){  
            try {  
                rst.close();  
            } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            }  
        }  
          
        if(pst!=null){  
            try {  
                pst.close();  
            } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            }  
        }  
          
        if(con!=null){  
            try {  
                con.close();  
            } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            }  
        }  
          
          
    }  
    private  void closeAllByst(Connection con,Statement st,ResultSet rst){  
        if(rst!=null){  
            try {  
                rst.close();  
            } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            }  
        }  
          
        if(st!=null){  
            try {  
            	st.close();  
            } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            }  
        }  
          
        if(con!=null){  
            try {  
                con.close();  
            } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            }  
        }  
          
          
    }  
/*    public  Result executeQuery(String sql){  
        Result result=null;  
        ResultSet rst=null;  
        PreparedStatement pst=null;  
        try {  
          
            pst=con.prepareStatement(sql);     
            Resultset rs = pst.executeQuery();

            while(rs.next()){
            	 
            }
            rs.close();
            pst.close();
            con.close();
             
              
        } catch (SQLException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }finally{  
            this.closeAll(con, pst, rst);  
        }  
          
        return result;  
    }  */
    public List<String> getListBySql(String sql) {
	 
		System.out.println("sql:" + sql);
		List<String> result = new ArrayList<>();
		Statement statement=null;
		ResultSet rs = null;
		try {
			statement = con.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				result.add(rs.getString(1));
				result.add(rs.getString(2));
				result.add(rs.getString(3));
				result.add(rs.getString(4));
			}
			rs.close();
			statement.close();
            con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			closeAllByst(con,statement,rs);
		}
		return result;
	} 
    
    /** 
     * 增删改 
     * @return 
     */  
    public int executeUpdate(String sql){  
        int result=-1;  
        PreparedStatement pst=null;  
        try {  
            pst=con.prepareStatement(sql);  
            /*if(sqlValues!=null&&sqlValues.size()>0){  //当sql语句中存在占位符时  
                setSqlValues(pst,sqlValues);  
            } */ 
            result=pst.executeUpdate();  
        } catch (SQLException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }finally{  
            this.closeAll(con, pst, null);  
        }
        return result;  
    }  
      
      
      
    /** 
     * 给sql语句中的占位符赋值 
     * @param pst 
     * @param sqlValues 
     */  
    private void setSqlValues(PreparedStatement pst,List sqlValues){  
        for(int i=0;i<sqlValues.size();i++){  
            try {  
                pst.setObject(i+1,sqlValues.get(i));  
            } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            }  
        }  
    }  
}  